package com.mairuide._frame.utils.sheethandler;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.jdbc.core.JdbcTemplate;

import com.mairuide._frame.utils.RegUtil;
import com.mairuide._frame.utils.SpringUtil;
import com.mairuide.bill.DealFlowService;

public class Excel03SheetHandlerMapping {
    private List<String> sqlList = new ArrayList<String>();
    private List<Integer> tempRowsNumber = new ArrayList<Integer>();
    public void deal(Workbook wb,Map<String , Object> paramMap){
        Iterator<Sheet> sheetIterator = wb.sheetIterator();
        while(sheetIterator.hasNext()){
            Sheet sheet = sheetIterator.next();
            int numberOfRows = sheet.getPhysicalNumberOfRows();
            for (int i = 0; i < numberOfRows; i++) {
                Row row = sheet.getRow(i);
                if (row==null) continue;
                int physicalNumberOfCells = row.getPhysicalNumberOfCells();
            	tempRowsNumber.add(physicalNumberOfCells);
                StringBuffer sbf = new StringBuffer("INSERT INTO TEMP_"+paramMap.get("uid")+" (");
                for(int b = 0 ; b < physicalNumberOfCells+1; b++ ) {
                	sbf.append("temp_"+b+",");
                }
                sbf.deleteCharAt(sbf.length() - 1);
                sbf.append(") ");
                for (int j = 0; j < physicalNumberOfCells; j++) {
                    Cell cell = row.getCell(j);
                    String val = "";
                    if (cell!=null) {
                    	 DataFormatter formatter = new DataFormatter();
                         val= RegUtil.replaceBlank(formatter.formatCellValue(cell));
                    }
                    if(j == 0) {
                    	sbf.append(" values('"+i+"',");
                    }
                    sbf.append("'"+val+"',");
                    
                }
                sbf.deleteCharAt(sbf.length() - 1);
                sbf.append(")");
                sqlList.add(sbf.toString());
                check(paramMap.get("uid").toString());
            }
        }
    }
    public void check(String uid){
        if (sqlList.size()>=10000){
            doSomething(uid);
            sqlList = new ArrayList<String>();
        }
    }

    public void doSomething(String uid){
    	JdbcTemplate jdbcTemplate = SpringUtil.getBean(JdbcTemplate.class);
    	DealFlowService dealFlowService = SpringUtil.getBean(DealFlowService.class);
    	//创建临时表
        dealFlowService.createTempTable(uid,tempRowsNumber);
        for (String sql : sqlList) {
        	jdbcTemplate.execute(sql);
        }
    }
    
	public List<String> getSqlList() {
		return sqlList;
	}
	public void setSqlList(List<String> sqlList) {
		this.sqlList = sqlList;
	}
	
}
